/************************************************************************************/
/*  Program name CAPARIMP_CUSTOMERS.sql                                     */
/*  Creater:  CHAN KA KEUNG BILLY (PCCW - UniHub)                                   */
/*************   History   *************************                                */
/* 09, MAY 2006 CHAN KA KEUNG BILLY     First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;
--ALTER TRIGGER VEH_RA_CUSTOMERS_T1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPARIMP_CUSTOMERS';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_RA_CUSTOMERS
	WHERE CUSTOMER_ID = 11267
	FOR UPDATE;

	lv_Next_Seq_ID	NUMBER;

BEGIN

	--  RENAME
	--DBMS_OUTPUT.Put_Line('1');
	UPDATE RA_CUSTOMERS
	SET CUSTOMER_NUMBER = CAPBRIMP_MAPPING.RenameStr(Substr(CUSTOMER_NUMBER,1,4),CUSTOMER_NUMBER)
	WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CRST_RA_CUSTOMERS);
	
	--DBMS_OUTPUT.Put_Line('2');
	UPDATE RA_CUSTOMERS
	SET ORIG_SYSTEM_REFERENCE = CAPBRIMP_MAPPING.RenameStr(Substr(ORIG_SYSTEM_REFERENCE,4,4),ORIG_SYSTEM_REFERENCE)
	WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CRST_RA_CUSTOMERS);
	
	FOR C1 IN Cur_F LOOP
			
	BEGIN
			SELECT	RA_CUSTOMERS_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;
			
			--DBMS_OUTPUT.Put_Line('3');
			UPDATE CRST_RA_CUSTOMERS
			SET CUSTOMER_ID_N = lv_Next_Seq_ID
			WHERE CUSTOMER_ID = C1.CUSTOMER_ID;
			
			--DBMS_OUTPUT.Put_Line('4');
			INSERT INTO RA_CUSTOMERS
			VALUES
			(
			C1.ROW_ID                         , 
			lv_Next_Seq_ID                     ,
			CAPARIMP_MAPPING.GetHZPartyIDMapping(C1.PARTY_ID),
			CAPARIMP_MAPPING.GetHZPartyNumberMapping(C1.PARTY_NUMBER),
			C1.PARTY_TYPE                      ,
			C1.LAST_UPDATE_DATE               , 
			C1.LAST_UPDATED_BY                 ,
			C1.CREATION_DATE                  , 
			C1.CREATED_BY                      ,
			C1.CUSTOMER_NAME                   ,
			C1.CUSTOMER_NUMBER                 ,
			C1.ORIG_SYSTEM_REFERENCE           ,
			C1.STATUS                          ,
			C1.LAST_UPDATE_LOGIN               ,
			C1.CUSTOMER_TYPE                   ,
			C1.CUSTOMER_PROSPECT_CODE          ,
			C1.CUSTOMER_CLASS_CODE             ,
			C1.PRIMARY_SALESREP_ID             ,
			C1.SALES_CHANNEL_CODE              ,
			C1.SIC_CODE                        ,
			C1.ORDER_TYPE_ID                   ,
			C1.PRICE_LIST_ID                   ,
			C1.ATTRIBUTE_CATEGORY              ,
			C1.ATTRIBUTE1                      ,
			C1.ATTRIBUTE2                      ,
			C1.ATTRIBUTE3                      ,
			C1.ATTRIBUTE4                      ,
			C1.ATTRIBUTE5                      ,
			C1.ATTRIBUTE6                      ,
			C1.ATTRIBUTE7                      ,
			C1.ATTRIBUTE8                      ,
			C1.ATTRIBUTE9                      ,
			C1.ATTRIBUTE10                     ,
			C1.REQUEST_ID                      ,
			C1.PROGRAM_APPLICATION_ID          ,
			C1.PROGRAM_ID                      ,
			C1.PROGRAM_UPDATE_DATE            , 
			C1.ANALYSIS_FY                     ,
			C1.CUSTOMER_CATEGORY_CODE          ,
			C1.CUSTOMER_GROUP_CODE             ,
			C1.CUSTOMER_KEY                    , 
			C1.CUSTOMER_SUBGROUP_CODE          ,
			C1.FISCAL_YEAREND_MONTH            ,
			C1.NET_WORTH                      ,
			C1.NUM_OF_EMPLOYEES               ,
			C1.POTENTIAL_REVENUE_CURR_FY      ,
			C1.POTENTIAL_REVENUE_NEXT_FY      ,
			C1.RANK                            ,
			C1.REFERENCE_USE_FLAG              ,
			C1.TAX_CODE                        ,
			C1.TAX_REFERENCE                   ,
			C1.ATTRIBUTE11                     ,
			C1.ATTRIBUTE12                     ,
			C1.ATTRIBUTE13                     ,
			C1.ATTRIBUTE14                     ,
			C1.ATTRIBUTE15                     ,
			C1.THIRD_PARTY_FLAG                ,
			C1.ACCESS_TEMPLATE_ENTITY_CODE     ,
			C1.PRIMARY_SPECIALIST_ID           ,
			C1.SECONDARY_SPECIALIST_ID         ,
			C1.COMPETITOR_FLAG                 ,
			C1.DUNNING_SITE_USE_ID            ,
			C1.STATEMENT_SITE_USE_ID          ,
			C1.ORIG_SYSTEM                     ,
			C1.YEAR_ESTABLISHED               ,
			C1.COTERMINATE_DAY_MONTH          ,
			C1.FOB_POINT                       ,
			C1.FREIGHT_TERM                    ,
			C1.GSA_INDICATOR                   ,
			C1.SHIP_PARTIAL                    ,
			C1.SHIP_VIA                        ,
			C1.WAREHOUSE_ID                    ,
			C1.PAYMENT_TERM_ID                 ,
			C1.TAX_EXEMPT                      ,
			C1.TAX_EXEMPT_NUM                  ,
			C1.TAX_EXEMPT_REASON_CODE          ,
			C1.JGZZ_FISCAL_CODE               ,
			C1.DO_NOT_MAIL_FLAG                ,
			C1.MISSION_STATEMENT              ,
			C1.CUSTOMER_NAME_PHONETIC         ,
			C1.TAX_HEADER_LEVEL_FLAG           ,
			C1.TAX_ROUNDING_RULE               ,
			C1.WH_UPDATE_DATE                 , 
			C1.GLOBAL_ATTRIBUTE1               ,
			C1.GLOBAL_ATTRIBUTE2               ,
			C1.GLOBAL_ATTRIBUTE3               ,
			C1.GLOBAL_ATTRIBUTE4               ,
			C1.GLOBAL_ATTRIBUTE5               ,
			C1.GLOBAL_ATTRIBUTE6               ,
			C1.GLOBAL_ATTRIBUTE7               ,
			C1.GLOBAL_ATTRIBUTE8               ,
			C1.GLOBAL_ATTRIBUTE9               ,
			C1.GLOBAL_ATTRIBUTE10              ,
			C1.GLOBAL_ATTRIBUTE11              ,
			C1.GLOBAL_ATTRIBUTE12              ,
			C1.GLOBAL_ATTRIBUTE13              ,
			C1.GLOBAL_ATTRIBUTE14              ,
			C1.GLOBAL_ATTRIBUTE15              ,
			C1.GLOBAL_ATTRIBUTE16              ,
			C1.GLOBAL_ATTRIBUTE17              ,
			C1.GLOBAL_ATTRIBUTE18              ,
			C1.GLOBAL_ATTRIBUTE19              ,
			C1.GLOBAL_ATTRIBUTE20              ,
			C1.GLOBAL_ATTRIBUTE_CATEGORY       ,
			C1.URL                            , 
			C1.LANGUAGE                        ,
			C1.TRANSLATED_CUSTOMER_NAME        ,
			C1.PERSON_PRE_NAME_ADJUNCT         ,
			C1.PERSON_FIRST_NAME               ,
			C1.PERSON_MIDDLE_NAME             ,
			C1.PERSON_LAST_NAME                ,
			C1.PERSON_SUFFIX                   ,
			C1.PERSON_FIRST_NAME_PHONETIC     ,
			C1.PERSON_LAST_NAME_PHONETIC      ,
			C1.SHIP_SETS_INCLUDE_LINES_FLAG    ,
			C1.ARRIVALSETS_INCLUDE_LINES_FLAG  ,
			C1.SCHED_DATE_PUSH_FLAG            ,
			C1.OVER_SHIPMENT_TOLERANCE        ,
			C1.UNDER_SHIPMENT_TOLERANCE       ,
			C1.OVER_RETURN_TOLERANCE          ,
			C1.UNDER_RETURN_TOLERANCE         ,
			C1.ITEM_CROSS_REF_PREF             ,
			C1.DATE_TYPE_PREFERENCE           ,
			C1.DATES_NEGATIVE_TOLERANCE       ,
			C1.DATES_POSITIVE_TOLERANCE       ,
			C1.INVOICE_QUANTITY_RULE           ,
			C1.ATTRIBUTE16                     ,
			C1.ATTRIBUTE17                     ,
			C1.ATTRIBUTE18                     ,
			C1.ATTRIBUTE19                     ,
			C1.ATTRIBUTE20                     ,
			C1.DUNS_NUMBER                    ,
			C1.DUNS_NUMBER_C                   ,
			C1.PARTY_LAST_UPDATE_DATE         , 
			C1.SIC_CODE_TYPE                                      
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	--COMMIT;
END;
/

--ALTER TRIGGER VEH_RA_CUSTOMERS_T1 ENABLE;
